package com.ibm;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.sql.Date;
import java.util.List;

import com.ibm.db.IBMETPer;

public class DBoperation {
	//插入数据库操作
	DBConnection dbc=new DBConnection();
	Connection conn=null;
//	PreparedStatement st=null;
	Statement st=null;
	ResultSet rs=null;
	
	//获取各个字段的数值
	
	
	public boolean persit(IBMETPer ibmer) throws SQLException, ClassNotFoundException{
		String name=ibmer.getName();; 
		int age =ibmer.getAge();
		Date birthday = new Date(ibmer.getBirthday().getTime());
		String ibmerID = ibmer.getIbmerID();
		String major = ibmer.getMajor();
		String certificateType = ibmer.getCertificateType();
		String college =ibmer.getCollege();
		
		String sql="insert into IBMETPer " +
				"(`name`, `age`, `birthday`, `ibmerID`, `major`, `certificateType`, `college`) values('"+name+"',"+age+
		",'"+ birthday +"','"+ibmerID+"','"+major+"','"
		+certificateType+"','"+college+"')";
		System.out.println(sql);
//		String sql="insert into ibmetper (name) values('li')"; 
		conn=dbc.getCnn();
//		st = conn.prepareStatement(sql);
		st= conn.createStatement();
//		ResultSet rs = null;
		st.executeQuery("set names utf8");
//		rs = st.executeQuery("show variables like '%char%'");
//		while(rs.next()){
//			System.out.println(rs.getString(1));
//			System.out.println(rs.getString(2));
//		}
		st.executeUpdate(sql);
		
		st.close();
		dbc.closeCnn();
		
		return true;
			
	}
	//在数据库查找数据
	@SuppressWarnings("unchecked")
	public List findAll() throws SQLException, ClassNotFoundException{

		List l =new ArrayList();
		String sql="select * from IBMETPer";
		conn=dbc.getCnn();
		st= conn.createStatement();
		rs = st.executeQuery(sql);
		while(rs.next()){
			IBMETPer ibmer = new IBMETPer();
			ibmer.setName(rs.getString("name"));
			ibmer.setAge(rs.getInt("age"));
			ibmer.setBirthday(rs.getDate("birthday"));
			ibmer.setIbmerID(rs.getString("ibmerID"));
			ibmer.setMajor(rs.getString("major"));
			ibmer.setCertificateType(rs.getString("certificateType"));
			ibmer.setCollege(rs.getString("college"));
			l.add(ibmer);
		}
		
		rs.close();
		st.close();
		dbc.closeCnn();
		return l;
		
	}
}
